package com.example.Project.JDBC;


import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.example.Project.Class.Apply;
import com.example.Project.Class.MassOrganizations;
import com.example.Project.Class.queryMass;
import com.example.Project.Class.User;
import com.example.Project.server.ConnectionPool;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCMethods {
    public static DruidDataSource dataSource;
    Connection connection = ConnectionPool.connect();

    public JDBCMethods() throws ClassNotFoundException {
    }

    private static DruidDataSource createDataSource(Properties properties) {
        try {
            return (DruidDataSource)DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception var2) {
            var2.printStackTrace();
            throw new RuntimeException("Failed to create DruidDataSource", var2);
        }
    }

    public static DruidDataSource getDataSource() {
        return dataSource;
    }

    //从数据库获取MassOrganization
    public ObservableList getMassOrganizations(int page) throws SQLException {
        int pageSize=17;
        ObservableList<MassOrganizations> data = FXCollections.observableArrayList();
        //起始行
        int startRow = (page - 1) * pageSize;
        String sql = "SELECT * FROM massorganizations LIMIT ?, ?";
        try (PreparedStatement pstmt = this.connection.prepareStatement(sql)) {
            pstmt.setInt(1, startRow);
            pstmt.setInt(2, pageSize);

            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                data.add(new MassOrganizations(
                        rs.getInt("Id"),
                        rs.getString("Name"),
                        rs.getDate("Birthday").toLocalDate(),
                        rs.getString("Type"),
                        rs.getString("Phone")
                ));
            }
        }
        return data;
    }
    public User getUserInfoById(int Id) throws SQLException {
        String sql = "SELECT * FROM users WHERE Id = ?";

        try (PreparedStatement pstmt = this.connection.prepareStatement(sql)) {
            pstmt.setInt(1, Id);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) { // Check if a result was returned
                User userInfo = new User(rs.getInt("id"),rs.getString("username"),rs.getString("usersex"),rs.getString("phone")); // Initialize the userInfo object
                return userInfo;
            } else {
                return null; // Or throw an exception if no result is expected
            }
        }
    }

    //获取总页数
    public int getTotalPage() throws SQLException {
        int pageSize = 17;
        String sql = "SELECT COUNT(*) FROM massorganizations";
        try (PreparedStatement pstmt = this.connection.prepareStatement(sql)) {
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                int totalItems = rs.getInt(1);
                return (int) Math.ceil((double) totalItems / pageSize);
            }
        }
        return 0;
    }
    //根据从数据库删除一条数据
    public void deleteMassOrganization(int id) throws SQLException {
        Statement stmt = this.connection.createStatement();
        String sql="DELETE FROM massorganizations WHERE Id = "+id+";";
        int rs = stmt.executeUpdate(sql);
        System.out.println(rs);
    }
    //根据id修改信息
    public void modifyMassOrganization(MassOrganizations mass) throws SQLException {
        System.out.println(mass.getId());
        String sql = "UPDATE massorganizations SET Name=?, Birthday=?, Type=?, Phone=? WHERE Id=?";
        try (PreparedStatement stmt = this.connection.prepareStatement(sql)) {
            stmt.setString(1, mass.getName());
            stmt.setDate(2, Date.valueOf(mass.getBirthday()));
            stmt.setString(3, mass.getType());
            stmt.setString(4, mass.getPhone());
            stmt.setInt(5, mass.getId());
            int rs = stmt.executeUpdate();
            System.out.println(rs);
        }
    }
    public ObservableList<MassOrganizations> queryMassOrganization(queryMass query) throws SQLException {
        StringBuilder sql= new StringBuilder("SELECT * FROM massorganizations WHERE 1 = 1");
        if(query.getId() != 0){
            sql.append(" AND id = "+query.getId());
        }
        if (query.getStart() != null) {
            sql.append(" AND birthday >= '"+query.getStart().toString()+"'");
        }
        if(query.getEnd()!=null){
            sql.append(" AND birthday <= '"+query.getEnd().toString()+"'");
        }
        if (query.getName() != null && !query.getName().isEmpty()) {
            sql.append(" AND (name LIKE '% "+query.getName()+"%' or name LIKE '"+query.getName()+"%' or name LIKE '%"+query.getName()+"')");
        }
        if (query.getPhone() != null && !query.getPhone().isEmpty()) {
            sql.append(" AND (phone LIKE '%"+query.getPhone()+"%' or phone LIKE '"+query.getPhone()+"%' or phone LIKE '%"+query.getPhone()+"')");
        }
        if (query.getType() != null && !query.getType().isEmpty()) {
            sql.append(" AND type = '"+query.getType()+"'");
        }
        sql.append(";");
        try (PreparedStatement pstmt = this.connection.prepareStatement(String.valueOf(sql))) {
            System.out.println(sql.toString());
            ResultSet rs = pstmt.executeQuery();
            ObservableList<MassOrganizations> data = FXCollections.observableArrayList();
            System.out.println("77777");
            while (rs.next()) {
                data.add(new MassOrganizations(
                        rs.getInt("Id"),
                        rs.getString("Name"),
                        rs.getDate("Birthday").toLocalDate(),
                        rs.getString("Type"),
                        rs.getString("Phone")
                ));
            }
            return data;
        }
    }
    public boolean addMassOrganization(MassOrganizations mass) throws SQLException {
        String sql = "INSERT INTO massorganizations (name, birthday, phone, type) VALUES (?, ?, ?, ?);";
        try (PreparedStatement pstmt = this.connection.prepareStatement(sql)) {
            pstmt.setString(1, mass.getName());
            pstmt.setString(2, String.valueOf(mass.getBirthday()));
            pstmt.setString(3, mass.getPhone());
            pstmt.setString(4, mass.getType());
            int rs = pstmt.executeUpdate();
            if(rs!=0){
                return true;
            }
            return false;
        }
    }
    public boolean login(String identity,String account,String password){
        String sql = null;
        if(identity.equals("用户")){
            sql = "SELECT * FROM users WHERE id = ?;";
        }
        if(identity.equals("管理员")){
            sql = "SELECT * FROM managers WHERE id = ?;";
        }
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            // 在准备的语句中设置账号参数
            pstmt.setString(1, account);
            // 执行查询并获取结果集
            ResultSet resultSet = pstmt.executeQuery();
            // 检查结果集是否有行
            if (resultSet.next()) {
                // 从数据库中获取存储的密码
                String storedPassword = resultSet.getString("password");
                // 将存储的密码与提供的密码进行比较
                if (password.equals(storedPassword)) {
                    // 密码匹配，登录成功
                    return true;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();  // 适当地处理 SQL 异常
        }
        return false;
    }

    /**
     * 根据用户id获取此用户加入的社团
     * @param id 用户
     * @return 此用户加入的社团列表
     * @throws SQLException
     */
    public ObservableList<MassOrganizations> getUserMassOrganizations(int id) throws SQLException {
        System.out.println(id);
        String sql = "SELECT * FROM massorganizations WHERE id IN (SELECT massid FROM `user-mass` WHERE userid = ? AND (ifagree = 1 OR ifagree = 2));";
        try (PreparedStatement stmt = this.connection.prepareStatement(sql)) {
            stmt.setInt(1, id);

            ResultSet rs = stmt.executeQuery();
            ObservableList<MassOrganizations> data = FXCollections.observableArrayList();
            System.out.println("77777");
            while (rs.next()) {
                data.add(new MassOrganizations(
                        rs.getInt("Id"),
                        rs.getString("Name"),
                        rs.getDate("Birthday").toLocalDate(),
                        rs.getString("Type"),
                        rs.getString("Phone")
                ));
            }
            return data;
        }
    }

    /**
     * 根据社团id获取此社团的成员
     * @param id 社团id
     * @return 社团成员列表
     * @throws SQLException
     */
    public ObservableList<User> getMassUser(int id) throws SQLException {
        System.out.println(id);
        String sql = "SELECT * FROM users WHERE id IN (SELECT userid FROM `user-mass` WHERE massid = ? AND (ifagree = 1 OR ifagree = 2));";
        try (PreparedStatement stmt = this.connection.prepareStatement(sql)) {
            stmt.setInt(1, id);

            ResultSet rs = stmt.executeQuery();
            ObservableList<User> data = FXCollections.observableArrayList();
            System.out.println("77777");
            while (rs.next()) {
                data.add(new User(
                        rs.getInt("id"),
                        rs.getString("username"),
                        rs.getString("usersex"),
                        rs.getString("phone")
                ));
            }
            return data;
        }
    }

    /**
     * 用户申请加入社团方法
     * @param userid 用户id
     * @param massid 社团id
     * @return  是否申请
     */
    public boolean userSendAdd(int userid,int massid) throws SQLException {
        String sql = "INSERT INTO `user-mass` (userid,massid,ifagree) SELECT ?, ?,0 FROM `user-mass` WHERE NOT EXISTS (SELECT 1 FROM `user-mass` WHERE userid = ? AND massid = ?);";
        try (PreparedStatement pstmt = this.connection.prepareStatement(sql)) {
            pstmt.setInt(1, userid);
            pstmt.setInt(2, massid);
            pstmt.setInt(3, userid);
            pstmt.setInt(4, massid);
            int rs = pstmt.executeUpdate();
            if(rs!=0){
                return true;
            }
            return false;
        }
    }

    /**
     * 用户申请退出社团方法
     * @param userid 用户id
     * @param massid 社团id
     * @return 是否申请
     * @throws SQLException
     */
    public boolean userSendDel(int userid,int massid,int i) throws SQLException {
        System.out.println(i);
        System.out.println(massid);
        System.out.println(userid);
        String sql = "UPDATE `user-mass` SET ifagree=? WHERE userid=? AND massid=?;";
        try (PreparedStatement pstmt = this.connection.prepareStatement(sql)) {
            pstmt.setInt(1, i);
            pstmt.setInt(2, userid);
            pstmt.setInt(3, massid);
            int rs = pstmt.executeUpdate();
            if(rs!=0){
                return true;
            }
            return false;
        }
    }

    /**
     * 根据用户id获取用户管理的社团
     * @param id 用户id
     * @return 用户管理的社团队列
     * @throws SQLException
     */
    public ObservableList<MassOrganizations> getMasterMass(int id) throws SQLException {
        System.out.println(id);
        String sql = "SELECT * FROM massorganizations WHERE masterid=?;";
        try (PreparedStatement stmt = this.connection.prepareStatement(sql)) {
            stmt.setInt(1, id);

            ResultSet rs = stmt.executeQuery();
            ObservableList<MassOrganizations> data = FXCollections.observableArrayList();
            System.out.println("77777");
            while (rs.next()) {
                data.add(new MassOrganizations(
                        rs.getInt("Id"),
                        rs.getString("Name"),
                        rs.getDate("Birthday").toLocalDate(),
                        rs.getString("Type"),
                        rs.getString("Phone")
                ));
            }
            return data;
        }
    }
    public ObservableList<Apply> getDelApply(int id) throws SQLException {
        System.out.println(id);
            String sql = "SELECT mo.Name AS organization_name,mo.Id AS organization_id, u.username, u.usersex, u.phone,u.id FROM massorganizations mo JOIN `user-mass` um ON mo.id = um.massid JOIN users u ON um.userid = u.id WHERE um.massid = ? AND um.ifagree=2;";
        try (PreparedStatement stmt = this.connection.prepareStatement(sql)) {
            stmt.setInt(1, id);

            ResultSet rs = stmt.executeQuery();
            ObservableList<Apply> data = FXCollections.observableArrayList();
            System.out.println("77777");
            while (rs.next()) {
                data.add(new Apply(
                        rs.getString("organization_name"),
                        rs.getString("username"),
                        rs.getString("usersex"),
                        rs.getString("phone"),
                        rs.getInt("organization_id"),
                        rs.getInt("id")
                ));
            }
            return data;
        }
    }
    public ObservableList<Apply> getAddApply(int id) throws SQLException {
        System.out.println(id);
        String sql = "SELECT mo.Name AS organization_name,mo.Id AS organization_id, u.username, u.usersex, u.phone ,u.id FROM massorganizations mo JOIN `user-mass` um ON mo.id = um.massid JOIN users u ON um.userid = u.id WHERE um.massid = ? AND um.ifagree=0;";
        try (PreparedStatement stmt = this.connection.prepareStatement(sql)) {
            stmt.setInt(1, id);

            ResultSet rs = stmt.executeQuery();
            ObservableList<Apply> data = FXCollections.observableArrayList();
            System.out.println("77777");
            while (rs.next()) {
                data.add(new Apply(
                        rs.getString("organization_name"),
                        rs.getString("username"),
                        rs.getString("usersex"),
                        rs.getString("phone"),
                        rs.getInt("organization_id"),
                        rs.getInt("id")
                ));
            }
            return data;
        }
    }
    public boolean deleteMassUser(int userid,int massid) throws SQLException {
        String sql = " DELETE FROM `user-mass` WHERE userid=? AND massid=?;";
        try (PreparedStatement pstmt = this.connection.prepareStatement(sql)) {
            pstmt.setInt(1, userid);
            pstmt.setInt(2, massid);
            int rs = pstmt.executeUpdate();
            if(rs!=0){
                return true;
            }
            return false;
        }
    }
    public int register(String name,String password,String sex,String phone) throws SQLException {
        String sql="INSERT INTO users (username,password,usersex,phone) SELECT ?, ?, ?, ? ;";
        try (PreparedStatement pstmt = this.connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS)) {
            pstmt.setString(1, name);
            pstmt.setString(2, password);
            pstmt.setString(3,sex);
            pstmt.setString(4,phone);
            int rs = pstmt.executeUpdate();
            if (rs > 0) {
                try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        int generatedId = generatedKeys.getInt(1);
                        return generatedId;
                    } else {
                        return -1;
                    }
                }
            } else {
                return -1;
            }
        } catch (SQLException e) {
            // 处理 SQL 异常
            e.printStackTrace();
            return -1;
        }
    }



    //创建类的时候连接数据库
    static {
        try {
            Properties properties = new Properties();
            InputStream inputStream = ConnectionPool.class.getClassLoader().getResourceAsStream("druid.properties");
            if (inputStream != null) {
                properties.load(inputStream);
                inputStream.close();
            } else {
                System.err.println("druid.properties file not found!");
            }

            dataSource = createDataSource(properties);
        } catch (Exception var2) {
            var2.printStackTrace();
        }
    }
}